﻿package org.yoao.crm.DBManager;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

/***
 * 数据库操作类
 *
 * @author Weishion
 * @time 2009-8-25 下午02:57:57
 */
public class DBManager {

	private static final Logger log = Logger.getLogger(DBManager.class);
	private static DBManager instance = new DBManager();
	private IPoolManagerService poolManager = null;

	public DBManager() {
		poolManager = new JNDIPoolManagerImpl();
	}

	public static DBManager getInstance() {
		return instance;
	}

	/***
	 * @author yxwonline
	 * @see 获取数据库连接
	 * @return Connection
	 */
	public Connection getConnection() {
		try {
			return poolManager.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	/****
	 * @author yxwonline
	 * @see 关闭数据库连接
	 */
	public void closeConnection(Connection con) {
		try {
			if (con != null && !con.isClosed()) {
				con.close();
				con = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/****
	 * @author yxwonline
	 * @see 操作数据库，增加，删除，修改
	 * @param sqlString
	 * @return boolean
	 */
	public int update(String sql) {
		if (sql == null || sql.trim().equals(""))
			return -1;
		log.debug(sql);

		int k = 0;
		Connection con = null;
		Statement state = null;
		try {
			con = getConnection();
			state = con.createStatement();
			k = state.executeUpdate(sql);
			state.close();
		} catch (Exception e) {
			k = -1;
			log.error("ERROR SQL:" + sql);
			e.printStackTrace();
		} finally {
			closeConnection(con);
			return k;
		}
	}

	/****
	 * @author yxwonline
	 * @see 返回ArrayList的查询， 查单个字段
	 * @param queryString
	 * @return
	 */
	public List querySingleColumn(String sql) {
		if (sql == null || sql.trim().equals(""))
			return null;
		log.debug(sql);

		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
		List result = new ArrayList();
		try {
			con = getConnection();
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(rs.getString(1));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			result = null;
			log.error("ERROR SQL:" + sql);
			e.printStackTrace();
		} finally {
			closeConnection(con);
			return result;
		}
	}

	public Map querySingleRow(String sql) {
		if (sql == null || sql.trim().equals(""))
			return null;
		log.debug(sql);

		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
		Map result = new HashMap();
		try {
			con = getConnection();
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			String columnName = null;
			while (rs.next()) {
				for (int i = 1; i <= columnCount; i++) {
					columnName = rsmd.getColumnName(i);
					result.put(columnName.toUpperCase(), rs
							.getString(columnName));
				}
			}
		} catch (SQLException e) {
			result = null;
			log.error("ERROR SQL:" + sql);
			e.printStackTrace();
		} finally {
			closeConnection(con);
			return result;
		}
	}

	public String querySingleValue(String sql) {
		if (sql == null || sql.trim().equals(""))
			return null;
		log.debug(sql);

		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
		String result = null;
		try {
			con = getConnection();
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					result = rs.getString(1);
					if (result != null)
						return result;
				}
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			result = null;
			log.error("ERROR SQL:" + sql);
			e.printStackTrace();
		} finally {
			closeConnection(con);
			return result;
		}
	}

	public List queryMultiColumn(String sql) {
		if (sql == null || sql.trim().equals(""))
			return null;
		log.debug(sql);

		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
		List result = new ArrayList();
		try {
			con = getConnection();
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			String columnName = null;
			while (rs.next()) {
				Map map = new HashMap();
				for (int i = 1; i <= columnCount; i++) {
					columnName = rsmd.getColumnName(i);
					map.put(columnName.toUpperCase(), rs.getString(columnName));
				}
				result.add(map);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			result = null;
			log.error("ERROR SQL:" + sql);
			e.printStackTrace();
		} finally {
			closeConnection(con);
			return result;
		}
	}

	/***
	 * @author 紫伟宸
	 * @time 2009-6-17 下午01:45:03
	 * @description 指定列查询 key大写
	 * @param sql
	 * @param columnNameArray
	 * @return List(Map(columnName))
	 * @throws SQLException
	 */
	public List queryAssignColumns(String sql, String[] columnNameArray) {
		if (sql == null || sql.trim().equals(""))
			return null;
		if (columnNameArray == null || columnNameArray.length < 1)
			return null;
		String temp = "select ";
		for (int i = 0; i < columnNameArray.length; i++) {
			if (i == (columnNameArray.length - 1))
				temp = temp + columnNameArray[i];
			else
				temp = temp + columnNameArray[i] + ",";
		}
		sql = temp + sql;
		log.debug(sql);

		Connection con = null;
		ResultSet rs = null;
		Statement st = null;
		List result = new ArrayList();
		try {
			con = getConnection();
			st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = st.executeQuery(sql);
			ResultSetMetaData meta = rs.getMetaData();
			int columnCount = meta.getColumnCount();
			String columnName = null;

			while (rs.next()) {
				Map map = new HashMap();
				for (int i = 1; i <= columnCount; i++) {
					columnName = meta.getColumnName(i);
					for (int k = 0; k < columnNameArray.length; k++) {
						if (columnName.equalsIgnoreCase(columnNameArray[k])) {
							map.put(columnName.toUpperCase(), rs
									.getString(columnName));
							break;
						}
					}
				}
				result.add(map);
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			result = null;
			log.error("ERROR SQL:" + sql);
			e.printStackTrace();
		} finally {
			closeConnection(con);
			return result;
		}
	}

	public String setNull(String str) {
		try {
			if (str == null)
				return "NULL";
			else
				return "'" + str + "'";
		} catch (Exception e) {
			return str;
		}
	}
}